unit jwb_tailor_DB;

interface

uses
  SysUtils,
  BaseApp,
  SQLite3,
  SQLite3Wrap,
  QuickSortList,
  QuickList_Int,
  jwb_define_dataobject;
  
type
  TAppDBData = record
    SqlDB: TSQLite3Database;
    CustomerList: TALIntegerList;
    ProjItemList: TALIntegerList;
  end;
  
  TAppDB = class(TBaseAppObj)
  protected
    fAppDBData: TAppDBData; 
    procedure LoadCustomerList;  
    procedure PostCustomerList;
    
    procedure LoadProjItemList; 
    procedure PostProjItemList;
    function CreateTableSQL_Project: string;    
    function CreateTableSQL_ProjItem: string;  
    function InsertTableSQL_Customer: string;
    function CreateTableSQL_Customer: string;
    function CreateTableSQL_Customer_Class: string; 
  public            
    constructor Create(App: TBaseApp); override; 
    destructor Destroy; override;
    function Initialize: Boolean;

    function NewCustomer(AKeyId: integer): PRT_Customer; 
    function NewProjItem(AKeyId: integer): PRT_ProjectItem;   
    procedure Post;
    property CustomerList: TALIntegerList read fAppDBData.CustomerList;   
    property ProjItemList: TALIntegerList read fAppDBData.ProjItemList; 
  end;
  
implementation

uses
  define_dbfield, define_db_app;
  
{ TAppDB }

constructor TAppDB.Create(App: TBaseApp);
begin
  inherited;
  FillChar(fAppDBData, SizeOf(fAppDBData), 0);  
  fAppDBData.CustomerList := TALIntegerList.Create;
  fAppDBData.CustomerList.Duplicates := lstDupAccept;
  
  fAppDBData.ProjItemList := TALIntegerList.Create;  
  fAppDBData.ProjItemList.Duplicates := lstDupAccept;
end;

destructor TAppDB.Destroy;
begin
  Post;
  inherited;
end;

function TAppDB.Initialize: Boolean;
var    
  tmpStr: string;
begin
  if nil = fAppDBData.SqlDB then
  begin
    fAppDBData.SqlDB := TSQLite3Database.Create;
  end;      
  tmpStr := 'dataset.db3'; //ChangeFileExt(ParamStr(0), '.db');
  fAppDBData.SqlDB.Open(tmpStr,
      SQLite3.SQLITE_OPEN_CREATE or
      SQLite3.SQLITE_OPEN_READWRITE or
      SQLite3.SQLITE_OPEN_NOMUTEX);
  fAppDBData.SqlDB.Execute(CreateTableSQL_Customer_Class);
  fAppDBData.SqlDB.Execute(CreateTableSQL_Customer);
  fAppDBData.SqlDB.Execute(CreateTableSQL_Project);
  fAppDBData.SqlDB.Execute(CreateTableSQL_ProjItem);
  LoadCustomerList;
  LoadProjItemList;
  Result := true;
end;
                                 
function TAppDB.CreateTableSQL_Customer_Class: string;
begin            
  Result := 'Create Table if not exists ' + Table_Customer_Class + '(' +
    Field_AutoKeyID   + FieldType_AutoIncKey + ',' +
    Field_KeyID       + FieldType_Int_Def + ',' +
    Field_ValidStatus + FieldType_Int_Def + ',' +
    Field_DataSrc     + FieldType_Int_Def + ',' +
    Field_Parent      + FieldType_Int_Def + ',' +
    Field_Name        + FieldType_String +
    ');';            
end;
               
function TAppDB.CreateTableSQL_Customer: string;
begin
  Result := 'Create Table if not exists ' + Table_Customer + '(' +
    Field_AutoKeyID   + FieldType_AutoIncKey + ',' +
    Field_KeyID       + FieldType_Int_Def + ',' +
    Field_ValidStatus + FieldType_Int_Def + ',' +
    Field_DataSrc     + FieldType_Int_Def + ',' +
    Field_Parent      + FieldType_Int_Def + ',' +   
    Field_Name        + FieldType_String + ',' +
    Field_Memo        + FieldType_String_Def + ',' +
    Field_Customer_No + FieldType_String_Def + ',' +
    Field_Customer_Contact + FieldType_String_Def + ',' +
    Field_Customer_Phone + FieldType_String_Def + ',' +
    Field_Customer_Balance + FieldType_Int_Def + ',' +
    Field_Customer_Address + FieldType_String_Def + ',' +
    Field_Value       + FieldType_String_Def +
    ');';            
end;
                  
function TAppDB.InsertTableSQL_Customer: string;
begin
  Result := ' insert into ' + Table_Customer +
                    ' (' +
                    Field_Name + ',' +
                    Field_Customer_No + ',' +
                    Field_Customer_Contact + ',' +
                    Field_Customer_Phone + ',' +
                    Field_Customer_Balance + ',' +
                    Field_Customer_Address + 
                    ') ' +
                    ' values ' +
                    ' (' +               
                    ':' + Field_Name + ',' +  
                    ':' + Field_Customer_No + ',' +
                    ':' + Field_Customer_Contact + ',' +
                    ':' + Field_Customer_Phone + ',' +
                    ':' + Field_Customer_Balance + ',' +
                    ':' + Field_Customer_Address + 
                    ') ';
end;

function TAppDB.CreateTableSQL_Project: string;
begin
  Result := 'Create Table if not exists ' + Table_Project + '(' +
    Field_AutoKeyID   + FieldType_AutoIncKey + ',' +
    Field_KeyID       + FieldType_Int_Def + ',' +   
    Field_ValidStatus + FieldType_Int_Def + ',' +  
    Field_DataSrc     + FieldType_Int_Def + ',' +    
    Field_Name        + FieldType_String + ',' +  
    Field_Memo        + FieldType_String_Def +
    ');';    
end;
                
function TAppDB.CreateTableSQL_ProjItem: string;
begin           
  Result := 'Create Table if not exists ' + Table_Project_Item + '(' +   
    Field_AutoKeyID   + FieldType_AutoIncKey + ',' +
    Field_KeyID       + FieldType_Int_Def + ',' +   
    Field_ValidStatus + FieldType_Int_Def + ',' +  
    Field_DataSrc     + FieldType_Int_Def + ',' +    
    Field_Name        + FieldType_String + ',' +  
    Field_Memo        + FieldType_String_Def +
    ');';    
end;

procedure TAppDB.LoadCustomerList;
var               
  tmpStr: string;
  tmpSelect: TSQLite3Statement;
  tmpCustomer: PRT_Customer;
  tmpCustomerInfo: PRT_CustomerInfo;
  tmpRet: integer; 
  tmpKeyId: integer;
begin
  tmpStr := ' select ' +
              Field_AutoKeyID + ',' +
              Field_Name + ',' +
              Field_Customer_No + ',' +
              Field_Customer_Contact + ',' +
              Field_Customer_Phone + ',' +
              Field_Customer_Balance + ',' +
              Field_Customer_Address + ',' +
              Field_Memo +
            ' from ' + Table_Customer;
  tmpSelect := fAppDBData.SqlDB.Prepare(tmpStr);
  if nil <> tmpSelect then
  begin
    tmpRet := tmpSelect.Step;
    while SQLITE_ROW = tmpRet do
    begin
      tmpKeyId := tmpSelect.ColumnInt(0);
      tmpCustomer := NewCustomer(tmpKeyId);
      if nil <> tmpCustomer then
      begin
        tmpCustomer.AutoKeyId := tmpKeyId;
        tmpCustomer.Name := tmpSelect.ColumnText(1);
        tmpCustomer.CustomNo := tmpSelect.ColumnText(2);
      end;
      tmpCustomerInfo := CheckOutCustomerInfo(tmpCustomer);
      tmpCustomerInfo.Contact := tmpSelect.ColumnText(3);
      tmpCustomerInfo.PhoneNum := tmpSelect.ColumnText(4);
      tmpCustomerInfo.Balance100 := tmpSelect.ColumnInt(5);
      tmpCustomerInfo.Address := tmpSelect.ColumnText(6);
      tmpCustomerInfo.Memo := tmpSelect.ColumnText(7);

      tmpRet := tmpSelect.Step;
    end;
  end;
end;

procedure TAppDB.LoadProjItemList;
var               
  tmpStr: string;
  tmpSelect: TSQLite3Statement;
  tmpProjItem: PRT_ProjectItem;
  tmpRet: integer; 
  tmpKeyId: integer;
begin
  tmpStr := ' select ' +
              Field_AutoKeyID + ',' +
              Field_Name + ',' +
              Field_Memo +
            ' from ' + Table_Project_Item;
  tmpSelect := fAppDBData.SqlDB.Prepare(tmpStr);
  if nil <> tmpSelect then
  begin
    tmpRet := tmpSelect.Step;
    while SQLITE_ROW = tmpRet do
    begin
      tmpKeyId := tmpSelect.ColumnInt(0);
      tmpProjItem := NewProjItem(tmpKeyId);
      if nil <> tmpProjItem then
      begin
        tmpProjItem.AutoKeyId := tmpKeyId;
        tmpProjItem.Name := tmpSelect.ColumnText(1);
        tmpProjItem.Memo := tmpSelect.ColumnText(2);
      end;
      tmpRet := tmpSelect.Step;
    end;
  end;
end;
                 
procedure TAppDB.PostProjItemList; 
var
  i: integer;
  tmpRet: integer;
  tmpProjItem: PRT_ProjectItem; 
  tmpInsert: TSQLite3Statement;    
  tmpUpdate: TSQLite3Statement;
  tmpStr: string;
begin
  for i := 0 to fAppDBData.ProjItemList.Count - 1 do
  begin
    tmpProjItem := PRT_ProjectItem(fAppDBData.ProjItemList.Objects[i]);
    if 0 = tmpProjItem.AutoKeyId then
    begin
      if '' <> Trim(tmpProjItem.Name) then
      begin
        tmpStr := ' insert into ' + Table_Project_Item +
                    ' (' +
                    Field_Name +
                    ') ' +
                    ' values ' +
                    ' (' +               
                    ':' + Field_Name +
                    ') '; 
        tmpInsert := fAppDBData.SqlDB.Prepare(tmpStr);
        if nil <> tmpInsert then
        begin
          tmpInsert.BindText(1, tmpProjItem.Name);  
          tmpRet := tmpInsert.Step;
          if (SQLITE_DONE = tmpRet) then
          begin
            tmpProjItem.AutoKeyId := fAppDBData.SqlDB.LastInsertRowID;
          end else
          begin
          end;
        end;
      end;
    end else
    begin
      if 0 <> tmpProjItem.UpdateVersion then
      begin
        tmpStr := ' UPDATE ' + Table_Project_Item +
                  ' SET ' +
                  Field_Name + '=:' + Field_Name + 
                  ' WHERE ' +
                  Field_AutoKeyID + '=' + inttostr(tmpProjItem.AutoKeyId);
        tmpUpdate := fAppDBData.SqlDB.Prepare(tmpStr);
        if nil <> tmpUpdate then
        begin
          tmpUpdate.BindText(1, tmpProjItem.Name);
        end;
      end;
    end;
  end;
end;

procedure TAppDB.PostCustomerList;
var
  i: integer;
  tmpRet: integer;
  tmpCustomer: PRT_Customer; 
  tmpInsert: TSQLite3Statement;    
  tmpUpdate: TSQLite3Statement;
  tmpStr: string;
begin
  for i := 0 to fAppDBData.CustomerList.Count - 1 do
  begin
    tmpCustomer := PRT_Customer(fAppDBData.CustomerList.Objects[i]);
    if 0 = tmpCustomer.AutoKeyId then
    begin
      if '' <> Trim(tmpCustomer.Name) then
      begin
        tmpStr := InsertTableSQL_Customer;
        tmpInsert := fAppDBData.SqlDB.Prepare(tmpStr);
        if nil <> tmpInsert then
        begin
          tmpInsert.BindText(1, tmpCustomer.Name);   
          tmpInsert.BindText(2, tmpCustomer.CustomNo);
          if nil <> tmpCustomer.Info then
          begin
            tmpInsert.BindText(3, tmpCustomer.Info.Contact);
            tmpInsert.BindText(4, tmpCustomer.Info.PhoneNum);    
            tmpInsert.BindInt(5, tmpCustomer.Info.Balance100);  
            tmpInsert.BindText(6, tmpCustomer.Info.Address);                   
          end else
          begin
            tmpInsert.BindText(3, '');
            tmpInsert.BindText(4, '');
            tmpInsert.BindInt(5, 0);  
            tmpInsert.BindText(6, '');            
          end;
          tmpRet := tmpInsert.Step;
          if (SQLITE_DONE = tmpRet) then
          begin
            tmpCustomer.AutoKeyId := fAppDBData.SqlDB.LastInsertRowID;
          end else
          begin

          end;
        end;
      end;
    end else
    begin
      if nil <> tmpCustomer.Info then
      begin
        if 0 <> tmpCustomer.Info.UpdateVersion then
        begin
          tmpStr := ' UPDATE ' + Table_Customer +
                    ' SET ' +
                    Field_Name + '=:' + Field_Name + ',' +
                    Field_Customer_No + '=:' + Field_Customer_No + ',' +
                    Field_Customer_Contact + '=:' + Field_Customer_Contact + ',' +
                    Field_Customer_Phone + '=:' + Field_Customer_Phone + ',' +
                    Field_Customer_Balance + '=:' + Field_Customer_Balance + ',' +
                    Field_Customer_Address + '=:' + Field_Customer_Address + 
                    ' WHERE ' +
                    Field_AutoKeyID + '=' + inttostr(tmpCustomer.AutoKeyId);
          tmpUpdate := fAppDBData.SqlDB.Prepare(tmpStr);
          if nil <> tmpUpdate then
          begin
            tmpUpdate.BindText(1, tmpCustomer.Name);  
            tmpInsert.BindText(2, tmpCustomer.CustomNo);  
            if nil <> tmpCustomer.Info then
            begin
              tmpInsert.BindText(3, tmpCustomer.Info.Contact);
              tmpInsert.BindText(4, tmpCustomer.Info.PhoneNum);
              tmpInsert.BindInt(5, tmpCustomer.Info.Balance100);
              tmpInsert.BindText(6, tmpCustomer.Info.Address);
            end;
            tmpRet := tmpUpdate.Step;
            if SQLITE_DONE = tmpRet then
            begin
              tmpCustomer.Info.UpdateVersion := 0;
            end;
          end;
        end;
      end;
    end;
  end;
end;

procedure TAppDB.Post;
begin               
  fAppDBData.SqlDB.BeginTransaction;
  try
    PostCustomerList;  
    PostProjItemList;
    fAppDBData.SqlDB.Commit;
  except
    fAppDBData.SqlDB.Rollback;
  end;
end;

function TAppDB.NewCustomer(AKeyId: integer): PRT_Customer;
begin
  Result := System.New(PRT_Customer);
  FillChar(Result^, SizeOf(TRT_Customer), 0);
  Result.AutoKeyId := AKeyId;
  fAppDBData.CustomerList.AddObject(AKeyId, TObject(Result));
end;

function TAppDB.NewProjItem(AKeyId: integer): PRT_ProjectItem;
begin
  Result := System.New(PRT_ProjectItem);
  FillChar(Result^, SizeOf(TRT_ProjectItem), 0);
  Result.AutoKeyId := AKeyId;
  fAppDBData.ProjItemList.AddObject(AKeyId, TObject(Result));
end;

end.
